package dbLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import modelLayer.Invoice;
import modelLayer.SalesOrder;
import dbLayer.DbPartner;

public class DbInvoice {
	private Connection con;
	
	public DbInvoice(){
		con = DbConnection.getInstance().getDBcon();
	}
	
	/**
	 * Method for inserting an invoice object into the database
	 * 
	 * @param inv The invoice object to be inserted
	 * @return Integer representing the rowsaffected
	 * @throws Exception 
	 */
	public int insertInvoice(Invoice inv) throws Exception{
		int rc = -1;
		/*java.sql.Date sqlDate = new java.sql.Date(inv.getPaymentDate().getDate());*/
		String query = "INSERT INTO invoice(paymentDate, customerId) VALUES('"+ inv.getPaymentDate() + "', " + inv.getCustomer().getPartnerId()+")";
		System.out.println(query);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}catch(SQLException sqle){
			System.out.println("Cant insert invoice.");
			throw new Exception("Invoice not inserted.");
		}
		return (rc);
	}
	
	/**
	 * Method for retrieving an invoice from the database on a certain customerId
	 * 
	 * @param customerId
	 * @return 
	 */
	public Invoice retrieveInvoiceFromDb(int customerId){
		String wClause = " customerId = " + customerId;
		//Skal checke betalingsdatoen, og returnere den nyeste faktura.
		return singleInvoice(wClause);
	}
	
	/**
	 * Method for getting all invoices from the database.
	 * 
	 * @return
	 */
	public ArrayList<Invoice> retrieveAllInvoices(){
		return multiInvoice("");
	}
	
	/**
	 * Method for retrieving an invoice from the invoice number.
	 * 
	 * @param id The invoice number from the wanted invoice
	 * @return The wanted invoice
	 */
	public Invoice retrieveInvoiceByInvoiceNo(int id){
		String tempId = Integer.toString(id);
		Invoice inv = singleInvoice(tempId);
		return inv;
	}
	
	/*
	 * Retrieve by sales order
	 * @param int orderId
	 * @return Invoice inv
	 * *//*
	public Invoice retrieveBySalesOrder(int orderId){
		DbSalesOrder dbso = new DbSalesOrder();
		SalesOrder so = dbso.retrieveOrderFromDb(orderId);
		buildSelectAllQuery(so.getOrderId())
		return retrieveInvoiceFromDb(so);
	}*/
	
	/**
	 * Method for building an invoice
	 * 
	 * @param results A resultset containing info about an invoice
	 * @return A invoice
	 */
	private Invoice buildInvoice(ResultSet results){
		Invoice inv = new Invoice();
		DbPartner dbpar = new DbPartner();
		try{
			inv.setInvoiceNo(results.getInt("invoiceNo"));
			inv.setCustomer(dbpar.retrieveCustomerFromDb(results.getInt("customerId")));
		}catch(Exception e){
			System.out.println("Cant create invoice object.");
		}
		return inv;
	}
	
	/**
	 * Method for retrieving a single invoice
	 * 
	 * @param where The SQL where clause
	 * @return Invoice 
	 */
	private Invoice singleInvoice(String where){
		Invoice inv = new Invoice();
		ResultSet results;
		
		String query = buildQuery(where);
		System.out.println(query);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next()){
				inv = buildInvoice(results);
				stmt.close();
			}else
				inv = null;
		}catch(Exception e){
			System.out.println("Cant create singleInvoice.");
		}
		return inv;
	}
	
	/**
	 * Method for creating multiple invoices.
	 * 
	 * @param where The SQL Where clause
	 * @return A list of invoices
	 */
	private ArrayList<Invoice> multiInvoice(String where){
		ArrayList<Invoice> list = new ArrayList<Invoice>();
		
		ResultSet results;
		String query = buildSelectAllQuery(where);
		System.out.println(query);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while(results.next()){
				Invoice inv = new Invoice();
				inv = buildInvoice(results);
				list.add(inv);
			}
			stmt.close();
		}catch(Exception e){
			System.out.println("Cant create multiple invoice objects.");
		}
		return list;
	}
	
	/**
	 * Method for building the query for the database
	 * 
	 * @param where The SQL where clause
	 * @return The complete query String
	 */
	private String buildQuery(String where){
		String query = "SELECT * FROM invoice ";

		if(where.length() > 0)
			query = query + "WHERE " + where;

		return query;
	}
	
	/**
	 * Method for building a SQL query, that select.
	 * 
	 * @param where SQL where clause
	 * @return The SQL query in a String
	 */
	private String buildSelectAllQuery(String where){
		String query = "SELECT * FROM invoice ";
		
		if(where.length() > 0)
			query = query + " WHERE " + where;
		
		return query;
	}
}
